<?php
/**
 * Class that operate on table 'transaction'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2010-11-29 23:51
 */
class TransactionMySqlExtDAO extends TransactionMySqlDAO implements TransactionExtDAO{

	/**
	 * 
	 * @see TransactionExtDAO::queryTransactionsViewThroughOptions()
	 * @param TransactionExtDAOOptions $options
	 */
	public function queryTransactionsViewThroughOptions( $options ) {			
		$sql = '
SELECT	
	transaction.*,											
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
	category.parentCategoryID,
        mark.name as markName,
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				{0}
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions
FROM transaction 						
LEFT JOIN account source on 	
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id
{1}
ORDER BY transaction.date ASC';
		
		$masterWhereArray = array();
		if ($options->accountID != null) {
			if ($options->onlySourceAccountID)
				$masterWhereArray[] = 'transaction.sourceAccountID = ' . $options->accountID;
			else if ($options->onlyDestinationAccountID)
				$masterWhereArray[] = 'transaction.destinationAccountID = ' . $options->accountID;
			else
				$masterWhereArray[] = '(transaction.sourceAccountID = ' . $options->accountID . ' or transaction.destinationAccountID = ' . $options->accountID . ')';
		}
		
		if ($options->temporaryID != null) {				
			$masterWhereArray[] = 'transaction.temporaryID = ' . $options->temporaryID;
		} else {
			if (!isset($options->temporaryID) || $options->temporaryID != 0) 
				$masterWhereArray[] = 'transaction.temporaryID IS NULL';
		}
			
		if ($options->parentTransactionID != null) {				
			$masterWhereArray[] = 'transaction.parentTransactionID = ' . $options->parentTransactionID;
		} else {
			if (!isset($options->parentTransactionID) || $options->parentTransactionID != 0) 
				$masterWhereArray[] = 'transaction.parentTransactionID IS NULL';
		}
		
		if ($options->startDate != null)
			$masterWhereArray[] = 'transaction.date >= \'' . $options->startDate . '\'';

		if ($options->endDate != null)
			$masterWhereArray[] = 'transaction.date <= \'' . $options->endDate . '\'';
			
		if ($options->categoryID != null)
			$masterWhereArray[] = 'category.id = ' . $options->categoryID;			
                
                if ($options->markID != null)
			$masterWhereArray[] = 'mark.id = ' . $options->markID;			                
			
		if ($options->parentCategoryID != null)
			$masterWhereArray[] = 'category.parentCategoryID = ' . $options->parentCategoryID;
			
		if ($options->annotation == null || !$options->annotation )
			$masterWhereArray[] = '(transaction.annotation IS NULL OR transaction.annotation = 0)';
		else
			$masterWhereArray[] = '(transaction.annotation = 1)';
		
		if ($options->notPrivateToPrivate != null && $options->notPrivateToPrivate)
			$masterWhereArray[] = 'NOT
								    (
								        (
								            destinationAccountID IS NOT NULL AND
								            destinationAccountID IN (SELECT account.id FROM account WHERE  accountType=\'1\')             
								        ) 
								        AND         
								        (
								            sourceAccountID IS NOT NULL AND
								            sourceAccountID IN (SELECT account.id FROM account WHERE  accountType=\'1\')         
								        )
								    )';
			
		$subWhereArray = array();
		if ($options->temporaryID != null) {				
			$subWhereArray[] = 'transaction.temporaryID = ' . $options->temporaryID;
		} else {
			if (!isset($options->temporaryID) || $options->temporaryID != 0) 
				$subWhereArray[] = 'transaction.temporaryID IS NULL';
		}
			
		if (count($subWhereArray) > 0)
			$sql = str_replace('{0}', join(' AND ', $subWhereArray) . ' AND ', $sql);
		else
			$sql = str_replace('{0}', '', $sql);
			
		if (count($masterWhereArray) > 0)
			$sql = str_replace('{1}', ' WHERE ' . join(' AND ', $masterWhereArray), $sql);
		else 
			$sql = str_replace('{1}', '', $sql);

		$sqlQuery = new SqlQuery($sql);
		DeveloperService::log('Query: ' . $sqlQuery->getQuery());
		return $this->getListView($sqlQuery);
	}

	public function queryTransactionsViewWhereAccountIDAndTemporaryID( $accountID, $temporaryID ) {
		$sql = '
SELECT	
	transaction.*,												
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
        mark.name as markName,        
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				subTransactions.temporaryID = ? and
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions
FROM transaction 						
LEFT JOIN account source on 	
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id                                        
WHERE
	transaction.temporaryID = ? and
	transaction.parentTransactionID IS NULL and
	(transaction.sourceAccountID = ? or transaction.destinationAccountID = ?) and
	(transaction.annotation IS NULL OR transaction.annotation = 0)
ORDER BY transaction.date ASC
		';			
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber( $temporaryID );
		$sqlQuery->setNumber( $temporaryID );
		$sqlQuery->setNumber( $accountID );
		$sqlQuery->setNumber( $accountID );
		return $this->getListView($sqlQuery);
	}
	
		public function queryTransactionsViewWhereAccountIDAndTemporaryIDAndParentTransactionID( $accountID, $temporaryID, $parentTransactionID ) {
		$sql = '
SELECT	
	transaction.*,					
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
        mark.name as markName,        
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				subTransactions.temporaryID = ? and
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions	
FROM transaction 						
LEFT JOIN account source on 
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id                                        
WHERE	
	transaction.temporaryID = ? and
	transaction.parentTransactionID = ? and
	(transaction.sourceAccountID = ? or transaction.destinationAccountID = ?) and
	(transaction.annotation IS NULL OR transaction.annotation = 0)
ORDER BY transaction.date ASC
		';			
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber( $temporaryID );
		$sqlQuery->setNumber( $temporaryID );
		$sqlQuery->setNumber( $parentTransactionID );
		$sqlQuery->setNumber( $accountID );
		$sqlQuery->setNumber( $accountID );
		return $this->getListView($sqlQuery);
	}
	
	public function queryTransactionsViewWhereAccountIDAndNotTemporary( $accountID ) {
		$sql = '
SELECT	
	transaction.*,
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
        mark.name as markName,        
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				subTransactions.temporaryID IS NULL and
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions	
FROM transaction
LEFT JOIN account source on 
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id                                        
WHERE	
	transaction.temporaryID IS NULL and
	transaction.parentTransactionID IS NULL and
	(transaction.sourceAccountID = ? or transaction.destinationAccountID = ?) and
	(transaction.annotation IS NULL OR transaction.annotation = 0)
ORDER BY transaction.date ASC
		';			
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber( $accountID );
		$sqlQuery->setNumber( $accountID );
		return $this->getListView($sqlQuery);
	}
	
	public function queryTransactionsViewWhereAccountIDAndNotTemporaryAndParentTransactionID( $accountID, $parentTransactionID ) {
		$sql = '
SELECT	
	transaction.*,
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
        mark.name as markName,        
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				subTransactions.temporaryID IS NULL and
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions	
FROM transaction
LEFT JOIN account source on 
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id                                        
WHERE	transaction.temporaryID IS NULL and
		transaction.parentTransactionID = ? and
		(transaction.sourceAccountID = ? or transaction.destinationAccountID = ?) and
		(transaction.annotation IS NULL OR transaction.annotation = 0)
ORDER BY transaction.date ASC		
		';			
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber( $parentTransactionID );
		$sqlQuery->setNumber( $accountID );
		$sqlQuery->setNumber( $accountID );
		return $this->getListView($sqlQuery);
	}

	public function hasSubtransactions( $transactionID ) {
		$sql = "SELECT COUNT(*) FROM transaction WHERE transaction.parentTransactionID = ?";
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber( $transactionID );
		$count = $this->querySingleResult($sqlQuery);
		return $count > 0 ? true : false;
	}

	public function updateTransactionsTemporaryID( $fromTemporaryID, $toTemporaryID ) {
		$sql = "UPDATE transaction SET transaction.temporaryID = ? WHERE transaction.temporaryID = ?";
		$sqlQuery = new SqlQuery($sql);			
		$sqlQuery->setNumber( $toTemporaryID );
		$sqlQuery->setNumber( $fromTemporaryID );
		return $this->executeUpdate($sqlQuery);
	}
	
	public function queryAllView() {
		$sql = 'SELECT transaction.id, transaction.amount, transaction.description, transaction.sourceAccountID, transaction.destinationAccountID, transaction.date, transaction.financialNumber,source.accountName as sourceAccountName,destination.accountName as destinationAccountName FROM transaction INNER JOIN account source on transaction.sourceAccountID = source.id INNER JOIN account destination on transaction.destinationAccountID = destination.id';
		$sqlQuery = new SqlQuery($sql);
		return $this->getListView($sqlQuery);
	}
	
	public function queryTransactionsOfAccountView($accountID) {
		$sql = 'SELECT transaction.id, transaction.amount, transaction.description, transaction.sourceAccountID, transaction.destinationAccountID, transaction.date, transaction.financialNumber,source.accountName as sourceAccountName,destination.accountName as destinationAccountName FROM transaction INNER JOIN account source on transaction.sourceAccountID = source.id INNER JOIN account destination on transaction.destinationAccountID = destination.id WHERE transaction.sourceAccountID = ? OR transaction.destinationAccountID = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setString($accountID);
		$sqlQuery->setString($accountID);
		return $this->getListView($sqlQuery);
	}
	
	public function queryBetweenDatesAndAccountNotParentAndNotTemporary( $startDate, $endDate, $accountID ) {
		$sql = '
SELECT	
	transaction.*,											
	source.accountName as sourceAccountName,
	destination.accountName as destinationAccountName,
	category.categoryName,
        mark.name as markName,        
    (        
        SELECT IF(COUNT(*) > 0, true, false) as subtransactionCount 
            FROM transaction as subTransactions
            WHERE   
				subTransactions.parentTransactionID = transaction.id
    )  as  hasSubtransactions
FROM transaction 						
LEFT JOIN account source on 	
	transaction.sourceAccountID = source.id 
		LEFT JOIN account destination on 
			transaction.destinationAccountID = destination.id
				LEFT JOIN category on
					transaction.categoryID = category.id
                                            LEFT JOIN mark on
                                                transaction.markID = mark.id                                        
WHERE	
	transaction.parentTransactionID IS NULL and
	transaction.temporaryID IS NULL and
	transaction.date between ? and ? and
	(transaction.sourceAccountID = ? or transaction.destinationAccountID = ?) and
	(transaction.annotation IS NULL OR transaction.annotation = 0)
ORDER BY transaction.date ASC
		';			
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setString( $startDate );
		$sqlQuery->setString( $endDate );
		$sqlQuery->setNumber( $accountID );
		$sqlQuery->setNumber( $accountID );
		return $this->getListView($sqlQuery);			
	}
	
	/**
	 * Read row (View)
	 *
	 * @return Transaction 
	 */
	protected function readRowView($row){
		$transaction = new TransactionView();
		
		$transaction->id = $row['id'];
		$transaction->sourceAccountID = $row['sourceAccountID'];
		$transaction->sourceAccountName = $row['sourceAccountName'];
		$transaction->destinationAccountID = $row['destinationAccountID'];
		$transaction->destinationAccountName = $row['destinationAccountName'];
		$transaction->amount = $row['amount'];
		$transaction->description = $row['description'];
		$transaction->bankMemo = $row['bankMemo'];
		$transaction->date = $row['date'];
		$transaction->financialNumber = $row['financialNumber'];
		$transaction->categoryID = $row['categoryID'];			
		$transaction->categoryName = $row['categoryName'];
		$transaction->temporaryID = $row['temporaryID'];
		$transaction->parentTransactionID = $row['parentTransactionID'];
		$transaction->hasSubtransactions = $row['hasSubtransactions'] > 0 ? true : false;
		$transaction->annotation = $row['annotation'];
                $transaction->markID = $row['markID'];
                $transaction->markName = $row['markName'];

		return $transaction;
	}
	
	protected function getListView($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		$ret = array();
		for($i=0;$i<count($tab);$i++){
			$ret[$i] = $this->readRowView($tab[$i]);
		}
		return $ret;
	}
}
?>